import json
import copy
import xlsxwriter
from io import BytesIO
from apis import api_routes
from public.local import date_type
from datetime import datetime, timedelta
from flask import g, request, jsonify, abort, Response
from security.decoration import certification
from typing import List
from typing import Union
from sqlalchemy import and_
from sqlalchemy import text
from urllib.parse import unquote, quote
from models.order.tasks import Tasks
from models.course.course import Course
from models.course.detailed import Detailed
from models.facility.facility import Facility
from models.course.serial import Serial


@api_routes.route("/quality", methods=["GET"])
@certification(auth=True, role=None)
def func_quality_get_api():
    """
    按照工站划分品质报表统计, 目前暂定这样开发，完成API开发
    http://127.0.0.1:8080/api/quality
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "facility_id"     int     ****      设备ID, 不传查询全部
    "start"           string            开始时间, 默认今天时间  格式 %Y-%m-%d
    "finish"          string            结束时间，默认今天时间  格式 %Y-%m-%d
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 品质统计
    """
    start = request.args.get("start", default=None, type=str)
    finish = request.args.get("finish", default=None, type=str)
    facility_id: Union[int] = request.args.get(key="facility_id", default=0, type=int)

    arg0, arg1 = start, finish
    parm: Union[int, None] = None
    facility: List[Facility] = g.session.query(Facility).filter(
                                Facility.uuid4 != parm
                            ).order_by(Facility.id).all()
    if facility_id > 0:
        facility: List[Facility] = g.session.query(Facility).filter(and_(
                                        Facility.id == facility_id,
                                        Facility.uuid4 != parm
                                    )).order_by(Facility.id).all()

    if finish is None or len(finish) == 0:
        arg1 = date_type() + timedelta(days=1) - timedelta(seconds=1)
    else:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)

    if start is None or len(start) <= 0:
        arg0 = date_type() - timedelta(days=30)
    else:
        arg0 = datetime.strptime(start, "%Y-%m-%d")

    start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
    finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")

    # SELECT
    #     COUNT(DISTINCT(sequence)) FROM detailed
    # WHERE time >= :arg0 AND time < :arg1 AND facility_id = :ft_id AND status1 = 'f'

    # sql_ngs: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM detailed
    #         INNER JOIN (
    #             SELECT
    #                 MAX(id) AS id, sequence
    #             FROM
    #                 detailed WHERE facility_id = :ft_id
    #                 AND time >= :arg0 AND time <= :arg1
    #             GROUP BY
    #                 sequence
    #         ) AS temps ON temps.id = detailed.id
    #     INNER JOIN courses ON courses.id = detailed.course_id
    #     WHERE courses.mark = 2 AND detailed.status = 'f'
    # """
    # sql_scraps: str = """
    #     SELECT
    #         COUNT(temps.sequence)
    #     FROM detailed
    #         INNER JOIN (
    #             SELECT
    #                 MAX(id) AS id, sequence
    #             FROM
    #                 detailed WHERE facility_id = :ft_id
    #                 AND time >= :arg0 AND time <= :arg1
    #             GROUP BY
    #                 sequence
    #         ) AS temps ON temps.id = detailed.id
    #     INNER JOIN courses ON courses.id = detailed.course_id
    #     WHERE courses.mark = 3 AND detailed.status = 'f'
    # """
    sql_ngs: str = """
            SELECT
                COUNT(temps.sequence)
            FROM ( 
                SELECT
                    sequence
                FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 AND mark = 2
            ) AS temps 
        """
    sql_scraps: str = """
            SELECT
                COUNT(temps.sequence)
            FROM ( 
                SELECT
                    sequence
                FROM detailed WHERE facility_id = :ft_id AND time >= :arg0 AND time <= :arg1 AND mark = 3
            ) AS temps 
        """

    ft_ids: list = []
    for itr in facility:
        ft_ids.append(itr.id)

    resp: list = []
    for ft_id in ft_ids:
        facility: Facility = g.session.query(Facility).filter(Facility.id == ft_id).first()

        parm: dict = {"arg0": start, "arg1": finish, "ft_id": ft_id}
        count_ngs: int = g.session.execute(text(sql_ngs), parm).fetchone()[0]
        count_scraps: int = g.session.execute(text(sql_scraps), parm).fetchone()[0]

        resp.append({
            "serials": facility.serials,
            "name": facility.name,
            "ngs": count_ngs,
            "scraps": count_scraps
        })

    docs: dict = {
        "serials": "设备编码",
        "name": "设备名称",
        "ngs": "不合格数量",
        "scraps": "报废数量"
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})
